import java.util.*;

import javax.swing.JFileChooser;

import com.mysql.jdbc.Messages;

import java.io.*;
import java.util.Scanner;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

public class Borrow {
	public static ArrayList<String> list3 = new ArrayList<String>();
	public static ArrayList<String> goodlist = new ArrayList<String>(); // Creating 3 ArrayList
	public static ArrayList<String> badlist = new ArrayList<String>();  //
	public static ArrayList<String> Authors = new ArrayList<String>();	//
	public static String ISBN1;
	static Connection connect;
	public static void main(String args[]) throws Exception {
		
		connect = DriverManager.getConnection("jdbc:mysql://localhost:3306/"+TaskDatabase.input+"", "root", TaskDatabase.password);
		System.out.println("Connected to Borrow Class " + TaskDatabase.input); 
		ArrayList<String> list1 = new ArrayList<String>();  // Creating an ArrayList that will contain  
															// the contents in file
		JFileChooser fileChooser = new JFileChooser();		// A chooser GUI for the user to choose the 
		if (fileChooser.showOpenDialog(null) == JFileChooser.APPROVE_OPTION) {//(if)Directory of the text file
			java.io.File file = fileChooser.getSelectedFile();
			Scanner sc = new Scanner(file); // Reading the file! 
			
			while (sc.hasNextLine()) {		// Reading in the file line after line
				list1.add(sc.nextLine());	// Storing the contents into an arraylist

			}	// End of the while loop
			sc.close();
			list(list1);					// calling the method list that 
											// checks the contents in the Arraylist
		}	// End of the if-statment

		else {
			System.out.println("No File selected"); // else-statment if the user doesn't choose a file
			System.exit(0);

		}
	}

	public static void list(ArrayList<String> list1) throws Exception {	// Method to check the contents in the Arraylist
		for (int i = 0; i < list1.size(); i++) {	// for-loop for the Arraylist checking every content
			String Error = "";						// Creating String to print out Error message
			if (Line(list1.get(i)) == false) {		// Calling the method Line to check if the lines have 8 contents
				Error += "The Book is missing some contents"; // if it have less than 8 contents write the error message
				badlist.add(Error + list1.get(i)); // Adding to the badlist arrarlist
				continue;				// continue doing the loop, don't stop
			}							// end the if-statment	

			Scanner input = new Scanner(list1.get(i)); //Read each line in the arraylist
			input.useDelimiter("#");		// read between the sign "#"

			Books Lists = new Books();		// Creating an Object to save the contents in the arraylist
											// 	then later to the goodlist Arralylist
			while (input.hasNext()) {		// 	while-loop to go throught each content

				String ISBN = input.next();		// Saving the ISBN into a String first .next();

				if (isDigits(ISBN) == true) {	// Boolean method to check if the isbn is digits
					if (checkValidISBN(ISBN) == true) { // Checking if the ISBN is valid
						Lists.setBooks1(ISBN);			// Saving the correct ISBN into setBooks1 created in Books-class
					} else {
						Error += "wrong ISBN";			// Else send error message. End of the second if-statment
					}

				} else {
					Error += "Wrong ISBN";				// Else send error message. End of the first if-statment

				}

				if (input.hasNext()) {					// if there is anything to read store it into String Copy
					String Copy = input.next();			
					if (cheking(Copy) == true) {		// checking if there is nothing between the sign # 

						if (isDigits(Copy) == true) {	// Checking if its a digit
							int Copyparse = Integer.parseInt(Copy);	// converting to integer from string
							Lists.setBooks2(Copyparse);		// Store it to the setBooks2

						} else {						// end of the if Statment
							Error += "Empty CopyNumber";// else return and Error from the third if

						}
					} else {						// end of the second if-statment
						Error += "Bad Copy";  		// Return an Error

					}

				} else {							// end of the first if-statment
					Error += "Bad Copy";			// return Error

				}
				String Title = input.next();		// Store to Title String
				if (cheking(Title) == true) {		// Checking if there is any space between the # sign
					Lists.setBooks3(Title);			// Store it to in the setBooks3

				} else {
					Error += "Bad Title";			// Return an Error

				}
				String Author = input.next();		// storing the Author into a string
				if (cheking(Author) == true) {		// checking if there is any space

					Lists.setBooks4(Author);		// Storing into setbooks4

				} else {
					Error = "Bad Author";			// Return Error message bad Author from the if

				}

				String Publisher = input.next();
				if (cheking(Publisher) == true) {		// Checking if there is empty space between the # sign
					Lists.setBooks5(Publisher);			// Store publisher it to setBooks5

				} else if (isDigits(Publisher) == true) { // else if the publisher isn't a String	
					Error += "Wrong Publisher";				// Send Error message

				} else {
					Error += "Wrong Publisher";				// send Error message

				}

				String Year = input.next();

				if (isDigits(Year) == true) {				// Check if Year is digits
					int number_year = Integer.parseInt(Year);	// Convert to integer
					Lists.setBooks6(number_year);		// Store it into setBooks6

					
				} else {								// Send Error Message Wrong Year
					Error += "Wrong Year";

				}
				String Stats = input.next();			
				if (isDigits(Stats)) {					// Check if its an integer 
					int parseStats = Integer.parseInt(Stats); // Convert it an integer
					Lists.setBooks7(parseStats);			  // Store it to setBooks7

				} else {
					Error += "Bad Stats";						// Send Error message

				}
				DateFormat df = new SimpleDateFormat("yyMMdd"); 	// creating format to yyMMdd  year(08), Month(02), Day(22),
				if (input.hasNext()) {								// if there is anything to read, do so
					String BorrowDate = input.next();				// Store into a String
					if (cheking(BorrowDate) == true) {				// check if there is any space
						if (ValidDate(BorrowDate) == true) {		// Method to check if its a vaild date
							Date date = (Date) df.parse(BorrowDate);	// Convert it to Date
							Lists.setBooks8(date);						// Store it into setBooks8

						} else {
							Error += "Bad BorrowDate";				// Else Error Message

						}

					} else {
						Error += "Bad BorrowDate";					// Else Error Message
					}
				}

				if (input.hasNext()) {								// Read next if there is anything to read 
					String ReturnDate = input.next();				// Store it into a String
					if (isDigits(ReturnDate)) {						// Check if its Digits
						if (ValidDate(ReturnDate) == true) {		// Check if its a vaild date
							Date date = (Date) df.parse(ReturnDate);	// Convert it into Date
							Lists.setBooks9(date);					// Store into setBooks9
						} else {
							Error += "Bad ReturnDate";				// Return Error Message
						}
					} else {
						Error += "Bad ReturnDate";					// Return Error Message
					}
				}

				if (input.hasNext()) {								// Read if there is anything to read 
					String LibraryCardNumber = input.next();		// Store it into a String	
					if (isDigits(LibraryCardNumber)) {				// Check if its a integer
						int parseLibraryCardNumber = Integer		// Convert it to integer
								.parseInt(LibraryCardNumber);		
						Lists.setBooks10(parseLibraryCardNumber);	// Store it into setBooks10

					}

					else {
						Error += "Bad LibraryCardNumber";			// Else Return a Error Message	

					}

				}

			
				if (Error.isEmpty())								//if there is no Errors Save every good content into an ArrayList
					goodlist.add(Lists.getFileFormat());
				else
					badlist.add(Error + list1.get(i));				// Else add the Errors into another ArrayList

			}
		
		}

		
			
		Bubblesort(goodlist, Authors);										// Calling a Method to sort the Authors
			
		
		
		
		
		
		
		for (int g = 0; g < goodlist.size(); g++) {	 // Go through the contents in the arraylist
			Scanner input = new Scanner (goodlist.get(g));
			input.useDelimiter("#");
			
			while(input.hasNext()){         // Saving the contents into int/String/Date
				ISBN1 = input.next();			
				String Copy = input.next();
				int CopyNumber = Integer.parseInt(Copy);
				String Title = input.next();
				String Author = input.next();
				String Publisher = input.next();
				String year = input.next();
				int Year = Integer.parseInt(year);
				String Stats = input.next();
				int Statictics =  Integer.parseInt(Stats);
				
				String borrow = input.next();
				DateFormat df = new SimpleDateFormat("yyMMdd");
				Date date1 = (Date) df.parse(borrow);
				java.sql.Date BorrowDate = new java.sql.Date(date1.getTime());
				
				
				if(input.hasNext()){
				String Return = input.next();
				Date date2 = (Date) df.parse(borrow);
				java.sql.Date ReturnDate = new java.sql.Date(date2.getTime());

				
				if(input.hasNext()){
				
				String CardNumber = input.next();
				int LibraryCardNumber = Integer.parseInt(CardNumber);
				
				
				// Inserting the values into the data base with the right data type for the Borrowers
				String sql = "INSERT INTO `dateBorrow`(`ISBN`, `BorrowDate`, `ReturnDate`, `LibraryCardNumber`) VALUES ( ? , ? , ? , ?)";
				PreparedStatement pst = TaskDatabase.con2.prepareStatement(sql);
				pst.setString(1, TaskDatabase.isbn.getText());
				pst.setDate(2, BorrowDate);
				pst.setDate(3, ReturnDate);
				pst.setInt(4, LibraryCardNumber);
				
				pst.execute();
				
				
				
				}
				} 
				// Inserting the Books from the arraylist into the database
			
				String query1 = "INSERT INTO books (`ISBN`, `CopyNumber`, `Title`, `Author`, `Publisher`, `Year`, `Statictics`)"
						+ "VALUES ('"
						+ ISBN1
						+ "','"
						+ CopyNumber
						+ "','"
						+ Title
						+ "','"
						+ Author
						+ "','"
						+ Publisher
						+ "','"
						+ Year
						+ "','"
						+ Statictics
						+"')";
			
				Statement stmt = connect.createStatement();
				stmt.executeUpdate(query1);		
				
				}
				}
			
	try{
		java.io.File file2 = new java.io.File("Borrowers.txt"); //Reading the file Borrowers
		Scanner get = new Scanner(file2); // Reading the file! 
		
		while (get.hasNextLine()) {		// Reading in the file line after line
			list3.add(get.nextLine());	// Saving into the arraylist
		}
		get.close();
	}catch(Exception e){
		
	}
	
	
	
	for(int j = 0; j<list3.size(); j++){ // Lopping through the contents
		Scanner in = new Scanner (list3.get(j));
		in.useDelimiter("#");
		
		while(in.hasNext()){	// Saving the contents from the arralist (Borrowers)
			String LibraryCardNumber = in.next();
			String Name = in.next();
			String Street = in.next();
			if(in.hasNext()){
				String ZipCode = in.next();
				
				String Town = in.next();
				
				// Inserting everything in the borrowers file into the database
				String query3 = "INSERT INTO borrow (`LibraryCardNumber`, `Name`, `Street`, `ZipCode`,`Town`)"
						+ "VALUES ('"
						+ LibraryCardNumber
						+ "','"
						+ Name
						+ "','"
						+ Street
						+ "','"
						+ ZipCode
						+ "','"
						+ Town+"')";
				
				Statement stmt = connect.createStatement();
				stmt.executeUpdate(query3); // Sending the query into the database
				
				
			}
		}
		
		
		}


}		
	

	public static boolean checkValidISBN(String ISBN) {		// Method to check if the ISBN is valid

		if (ISBN.length() == 13) {				// First checking if the length if the string is 13(ISBN-13)

			long number = Long.parseLong(ISBN); // Then convert to Long
			int LastNumber = (int) (number % 10);	// Get the last number

			number = (number - LastNumber) / 10;	// Get 12 digits

			int sum = 0;						// integer sum with value 0
			int multiply = 3;					// integer multiply with value 3

			while (number > 0) {				// While loop continue if number is more than 0
				int right = (int) (number % 10);		// Taking last number in the 12 digits
				sum = sum + (right * multiply);			// multiply by 3
				number = (number - right) / 10;			// 11 Digits
				if (multiply == 3) {					// if it have multiplied by 3 then multiply by 1
					multiply = 1;					
				} else {								// else multiply by 3
					multiply = 3;
				}
			}

			if ((sum % 10) == (10 - LastNumber)) {			// Checking with the last number return true if they are equal
				return true;

			} else {										// else false
				return false;

			}
		} else {											// Return false if the first if-statement is not true
			return false;
		}

	}

	public static boolean isDigits(String r) { // Method to convert to digits
		try {								// Try catch method

			long s = Long.parseLong(r);		// Convert to digits
			return true;					// Return true
		} catch (NumberFormatException e) {	// catch the wrong exception

			e.getMessage();					
			return false;					// Return false
		}

	}

	public static boolean cheking(String space) {	// Method to check if there is any space
		if (!space.isEmpty()) {					// if its not empty
			return true;						// then return true

		} else {
			return false;						// else return false
		}
	}

	public static boolean ValidDate(String valid) {		// Method to check if its a valid Date

		try {
			DateFormat df = new SimpleDateFormat("yyMMdd");		// Creating format to yyMMdd
			Date date_ = (Date) df.parse(valid);				// Convert the String to Date

			if (df.format(date_).equals(valid))					// Compare if its equal to Dateformat
				return true;

			else
				return false;
		}

		catch (ParseException e) {
			return false;
		}
	}



	public static boolean Line(String input) {				// Boolean Method to check the lines, if they have 8 contents
		Scanner s = new Scanner(input).useDelimiter("#");	// Read after each sign #
		int count = 0;										// create a counter
		while (s.hasNext()) {								// if there is anything to read
			String trash = s.next();						// save it to trash string
			count++;										// read and continue the count
		}
		if (count >= 8) {									// check if the count is more equal to 8
			return true;									// then true 
		}

		else {
			return false;									// Else false return
		}

	}

	public static ArrayList<String> Bubblesort(ArrayList<String> goodlist,	// Method to sort the Authors
			ArrayList<String> Authors) throws Exception {

		for (int i = 0; i < goodlist.size(); i++) {				// get the contents from the goodlist(ArrayList)

			Scanner input = new Scanner(goodlist.get(i));		// go through every line
			input.useDelimiter("#");							// read every content after each # sign
			input.next();										// read till reaching author 
			input.next();
			input.next();
			Authors.add(input.next());							// save the authors in the 
																// goodlist into a new Arraylist called Authors
		}

		for (int a = 0; a < Authors.size(); a++) {				//Creating 2 for loops to go compareroot
			for (int i = 0; i < Authors.size() - 1; i++) {
				if (Authors.get(i).compareTo(Authors.get(i + 1)) > 0) {		// Comparing the Authors if bigger then 0 go to next step

					String temp = goodlist.get(i);							
					goodlist.set(i, goodlist.get(i + 1));				 
					goodlist.set((i + 1), temp);							
																			// Create temp String and start swaping
																			// The i will be connecting both Arraylists 
					String temps = Authors.get(i);
					Authors.set(i, Authors.get(i + 1));
					Authors.set((i + 1), temps);

				}
			}

		}

		return Authors;

	}
	public static boolean checkbook(String ISBN )throws Exception {
		
		
		// Connecting to the databse
		connect = DriverManager.getConnection("jdbc:mysql://localhost:3306/"+TaskDatabase.input, "root", TaskDatabase.password);
		
		Statement stmt = connect.createStatement(); 
		ResultSet rs = stmt.executeQuery("select * from books where ISBN='" // Query to find the ISBN the user inputs
				+ ISBN 
				+ "'");
		
		int CopyNumber ;
		String Title = "";		
		String Author = "", Publisher = "";
		int Year;
		int Statictics;
		 
		if (rs.next()) {	// Getting the contents needed from the databse
			ISBN = rs.getString("ISBN");
			CopyNumber = rs.getInt("CopyNumber");
			Title = rs.getString("Title");
			Author = rs.getString("Author");
			Year = rs.getInt("Year");
			Statictics = rs.getInt("Statictics");
			
			TaskDatabase.searchName.setText(Title +" "+ Author +" "+ Year);
			TaskDatabase.pnlSearchN.add(TaskDatabase.searchName); // adding to the searching by ISBN
			
			
			return true;
			
		}else{
			System.out.println("ISB Not Found!"); // Error messege if the ISBN is not found
			return false;
		}
		
		
		
	}
	public static boolean LibraryCardNumber(String Card) throws SQLException{
		
		
		// Connecting to the databse and getting the LibraryCardNumber
		int LibraryCardNumber = Integer.parseInt(Card);
		connect = DriverManager.getConnection("jdbc:mysql://localhost:3306/"+TaskDatabase.input+"", "root", TaskDatabase.password);
		Statement stmt = connect.createStatement(); 
		ResultSet rs = stmt.executeQuery("select * from borrow where LibraryCardNumber='"
				+ LibraryCardNumber
				+ "'");
		
		
		// If the LibraryCardNumber exists then return true
		if (rs.next()) {// Getting the LibraryCardNumber from the database and saving it
			LibraryCardNumber = rs.getInt("LibraryCardNumber");
			
			return true;
			
		}else{
			
			return false;
		}
		
		
	}
	public static boolean checkCopyNumber(String Copy) throws SQLException{
	
		Copy = TaskDatabase.isbn.getText().toString();
		// Connecting to the database and get the ISBN from the user and find it in the table books
		// and then get the CopyNumber for that ISBN(Book)
		connect = DriverManager.getConnection("jdbc:mysql://localhost:3306/"+TaskDatabase.input+"", "root", TaskDatabase.password);
		Statement stmt = connect.createStatement(); 
		ResultSet rs = stmt.executeQuery("select * from books where ISBN='"
				+ Copy
				+ "'");
		
		int CopyNumber;
		String ISBN = "";
		
		
		if (rs.next()) { // getting the CopyNumber
			ISBN = rs.getString("ISBN");
			CopyNumber= rs.getInt("CopyNumber");
			System.out.println(CopyNumber);
			
			if(CopyNumber != 0){// if the copynumber not = to 0 then decrease it with 1(CopyNumber-1)
				CopyNumber -= 1;
				 // and update the change into the databse where ISBN and CopyNumber in the same row
				String query4 = ("UPDATE books SET CopyNumber ='"+CopyNumber+"' where ISBN='" +Copy +"'");
						
			
				Statement stmt1 = connect.createStatement();
				stmt1.executeUpdate(query4);		// Execute the change
				return true; // if all the statments are true then return true else false
			}else{
				return false;
			}
			
			
		}else{
			
			return false;
		
	}
}
}
